﻿using IDAdmin.Lib.Utils;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Web.Script.Serialization;

namespace IDAdmin.Lib.DataLayer
{
    public class LevelSaveStatsWebDB
    {
        public const string hostName = "222.255.177.27";
        public const int port = 9823;
        List<Au> AccountALlList = new List<Au>();
        List<ServerDTO> gameServerList;//= new List<ServerDTO>() { new ServerDTO() { Name = "vn03", StartSeverDate = "2015-03-12 00:00:00" }, new ServerDTO() { Name = "vn04", StartSeverDate = "2015-04-14 00:00:00" } };
        public LevelSaveStatsWebDB()
        {
            gameServerList = WebDB_N.ListServer().Select(s => new ServerDTO() { GameZone = s.gamezone.ToString(), Name = s.tablezone, StartSeverDate = s.created.ToString("yyyy-MM-dd HH:mm:ss") }).ToList();
        }

        public static int testA7(DateTime date)
        {
            List<Au> a7List = new List<Au>();
            DateTime beforeSixthDay = date.AddDays(-6);
            using (SqlConnection conn = ConnectionHelper.GetConnection())
            {
                double fromTS = DataToolUtil.kindDateTimeToKindUnixTimestamp(DateTime.ParseExact(beforeSixthDay.ToString("yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), DateTimeKind.Local);
                double toTS = DataToolUtil.kindDateTimeToKindUnixTimestamp(DateTime.ParseExact(date.ToString("yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), DateTimeKind.Local);
                string cmdText = "Select distinct(account) as account,gamezone as account from au where timestamp >= {0} and timestamp <= {1}";
                cmdText = string.Format(cmdText, fromTS, toTS);
                SqlCommand command = new SqlCommand(cmdText, conn);
                command.CommandTimeout = 3600;
                SqlDataReader dataReader = command.ExecuteReader();
                while (dataReader.Read())
                {
                    a7List.Add(new Au() { Account = dataReader.GetString(0).ToLower(), GameZone = dataReader.GetString(1) });
                }
                dataReader.Close();
            }
            return a7List.Count;
        }
        public void getStatsData(DateTime date)
        {
            List<LevelAndPeopleNum> levelSaveStatsData = new List<LevelAndPeopleNum>();
            // List<Au> a7List = new List<Au>();
            List<Au> a7List = new List<Au>();
            //DateTime nextDtTemp = date.AddDays(1);
            DateTime beforeSixthDay = date.AddDays(-6);

            TimeSpan difference = DateTime.Now.Date - date.Date;
            int days = (int)difference.TotalDays;
            if (days >= 1)
            {
                using (SqlConnection conn = ConnectionHelper.GetConnection())
                {
                    double fromTS = DataToolUtil.kindDateTimeToKindUnixTimestamp(DateTime.ParseExact(beforeSixthDay.ToString("yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), DateTimeKind.Local);
                    double toTS = DataToolUtil.kindDateTimeToKindUnixTimestamp(DateTime.ParseExact(date.ToString("yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), DateTimeKind.Local);
                    string cmdText = "Select distinct(account) as account,gamezone as account from au where timestamp >= {0} and timestamp <= {1}";
                    cmdText = string.Format(cmdText, fromTS, toTS);
                    SqlCommand command = new SqlCommand(cmdText, conn);
                    command.CommandTimeout = 3600;
                    SqlDataReader dataReader = command.ExecuteReader();
                    while (dataReader.Read())
                    {
                        a7List.Add(new Au() { Account = dataReader.GetString(0).ToLower(), GameZone = dataReader.GetString(1) });
                    }
                    dataReader.Close();
                }
                LevelWebDB.delete_A7_Table(date.ToString("yyyy-MM-dd") + " 00:00:00", date.ToString("yyyy-MM-dd") + " 23:59:59");
                foreach (var account in a7List)
                {
                    string url = "http://{0}:{1}/getCharidName?gosuAccount={2}&game=38&zone={3}";
                    url = string.Format(url, hostName, port, account.Account, account.GameZone);
                    string result = HttpHelper.HttpSocket(url, 26);
                    if (string.IsNullOrEmpty(result) == false)
                    {
                        if (result.Substring(0, 1).Equals("0"))
                        {
                            JavaScriptSerializer jss = new JavaScriptSerializer();
                            //CharidName cin = jss.Deserialize<CharidName>(result.Substring(2, result.IndexOf("}", result.IndexOf("name")) - 1));
                            string charid = result.Substring(result.IndexOf("charid") + 10, (result.IndexOf(",", result.IndexOf(",") + 1) - (result.IndexOf("charid") + 10)) - 1);
                            LevelWebDB.insert_A7_Table("A7", date, account.Account, charid, account.GameZone);
                            //string level = LevelWebDB.getLevel(cin.charid, "2015-03-12 00:00:00", dtTemp.ToString("yyyy-MM-dd") + " 23:59:59");
                            //accountAndLevelList.Add(new { Account = account.Account, Level = level });
                        }
                        else
                        {
                            //LevelWebDB.insert_A7_Table("A7", date, account.Account, "", account.GameZone);
                        }
                    }
                }
                //levelSaveStatsData = getLevelSaveStats(date);
            }
            //return levelSaveStatsData;
        }


        public List<LevelAndPeopleNum> getLevelSaveStats(DateTime date)
        {
            List<LevelAndPeopleNum> lvAndPeoNumList = new List<LevelAndPeopleNum>();
            List<string> levelList = new List<string>();
            Dictionary<string, List<LevelAndPeopleNum>> lvAndPeoNumDic = new Dictionary<string, List<LevelAndPeopleNum>>();
            try
            {
                foreach (var server in gameServerList)
                {
                    using (SqlConnection conn = ConnectionHelper.GetConnection())
                    {
                        SqlCommand command = new SqlCommand();
                        command.CommandTimeout = 3600;
                        command.CommandType = CommandType.Text;
                        command.Connection = conn;
                        string commandText = "select COUNT(tb.charid) as amount,TB.lv as level from (select le.charid,max(level) as lv from Level{0} as le join A7 as a7 on le.charid=a7.charid   WHERE a7.GameZone={1} and le.datetime between '{2}' and '{3}' and a7.datetime between '{4}' and '{5}' group by le.charid) as tb group by tb.lv";

                        string commandText2 = string.Format(commandText, server.Name, server.GameZone, server.StartSeverDate, date.ToString("yyyy-MM-dd") + " 23:59:59", date.ToString("yyyy-MM-dd") + " 00:0:00", date.ToString("yyyy-MM-dd") + " 23:59:59");
                        command.CommandText = commandText2;
                        try
                        {
                            SqlDataReader dataReader = command.ExecuteReader();
                            List<LevelAndPeopleNum> list = new List<LevelAndPeopleNum>();
                            while (dataReader.Read())
                            {
                                LevelAndPeopleNum lap = new LevelAndPeopleNum();
                                lap.Level = dataReader.GetInt64(1).ToString();
                                levelList.Add(lap.Level);
                                lap.PeopleNum = dataReader.GetValue(0).ToString();
                                list.Add(lap);
                            }
                            dataReader.Close();
                            lvAndPeoNumDic.Add(server.Name, list);
                        }
                        catch (Exception ex)
                        {

                        }
                    }
                }
                levelList = levelList.Distinct().ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            List<LevelAndPeopleNum> levAndPeopListInterme = lvAndPeoNumDic.SelectMany(s => s.Value).ToList();
            foreach (var level in levelList)
            {
                LevelAndPeopleNum lapn = new LevelAndPeopleNum();
                lapn.PeopleNum = levAndPeopListInterme.Where(w => w.Level.Equals(level)).Sum(s => int.Parse(s.PeopleNum)).ToString();
                lapn.Level = level;
                lvAndPeoNumList.Add(lapn);
            }
            return lvAndPeoNumList;
        }

        public void insertStatsData(List<LevelAndPeopleNum> data, DateTime date)
        {
            using (SqlConnection conn = ConnectionHelper.GetConnection())
            {//sp_Insert_PremiumLogin
                SqlCommand deleteCommand = new SqlCommand("spTransfer_delete_LevelSave", conn);
                deleteCommand.CommandTimeout = 3600;
                deleteCommand.CommandType = CommandType.StoredProcedure;
                deleteCommand.Parameters.AddWithValue("@fromDT", date.ToString("yyyy-MM-dd") + " 00:00:00");
                deleteCommand.Parameters.AddWithValue("@toDT", date.ToString("yyyy-MM-dd") + " 23:59:59");
                deleteCommand.ExecuteNonQuery();
            }
            foreach (LevelAndPeopleNum item in data)
            {
                try
                {
                    using (SqlConnection conn = ConnectionHelper.GetConnection())
                    {
                        SqlCommand command = new SqlCommand("sp_Insert_LevelSave", conn);
                        command.CommandTimeout = 3600;
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@datetime", date);
                        command.Parameters.AddWithValue("@Level", item.Level);
                        command.Parameters.AddWithValue("@peoplenum", item.PeopleNum);
                        command.ExecuteNonQuery();
                    }
                }
                catch (Exception ex)
                {

                }
            }
        }
        public List<LevelAndPeopleNum> getFastStatsData(string from, string to)
        {
            List<LevelAndPeopleNum> data = new List<LevelAndPeopleNum>();
            using (SqlConnection conn = ConnectionHelper.GetConnection())
            {
                SqlCommand command = new SqlCommand("sp_Select_LevelSave", conn);
                command.CommandTimeout = 3600;
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@fromDT", from);
                command.Parameters.AddWithValue("@toDT", to);
                SqlDataReader dataReader = command.ExecuteReader();
                while (dataReader.Read())
                {
                    LevelAndPeopleNum lapn = new LevelAndPeopleNum();
                    //lapn.Date = dataReader.GetDateTime(0).ToString("dd/MM/yyyy");
                    lapn.Level = dataReader.GetInt64(1).ToString();
                    lapn.PeopleNum = dataReader.GetInt64(2).ToString();
                    data.Add(lapn);
                }
                dataReader.Close();
            }
            return data;
        }
    }
}
